# 数据库操作模块
import pymysql
import pymysql.cursors
from pydantic import BaseModel
from typing import Optional
import uvicorn
from fastapi.middleware.cors import CORSMiddleware


# 数据库配置
DB_CONFIG = {
    "host": "localhost",       # 数据库地址
    "user": "root",            # 数据库用户名
    "password": "0217",    # 数据库密码
    "database": "mydb",        # 数据库名
    "charset": "utf8mb4",
    "connect_timeout": 10      # 添加连接超时设置
}
# 定义视频数据模型
class Video(BaseModel):
    video_id: str  # 视频ID，主键 - 必需字段
    title: str  # 视频标题 - 必需字段
    path: str  # 视频路径 - 必需字段
    publish_time: str  # 发布时间 - 必需字段
    author_id: str  # 作者ID - 必需字段
    duration: float  # 视频时长 - 必需字段
    deleted: Optional[bool] = False  # 视频是否已删除 - 可选
    category: Optional[str] = None  # 视频分类 - 可选
    description: Optional[str] = None  # 视频描述 - 可选
    remarks: Optional[str] = None  # 备注信息 - 可选
    likes: Optional[int] = 0  # 点赞数 - 可选，默认0
    favorites: Optional[int] = 0  # 收藏数 - 可选，默认0
    reposts: Optional[int] = 0  # 转发数 - 可选，默认0

class VideoRequest(BaseModel):
    op: str   # add / modify / delete
    video: Video
    
# 数据库连接函数
def get_db_connection():
    """获取数据库连接"""
    try:
        connection = pymysql.connect(**DB_CONFIG, cursorclass=pymysql.cursors.DictCursor)
        return connection
    except pymysql.err.OperationalError as e:
        raise
    except Exception as e:
        raise

# 初始化数据库表，支持检查和自动修正表结构
def init_database():
    """初始化数据库表，支持表结构检查和自动修正"""
    # 定义标准表结构
    standard_columns = {
        'video_id': {'type': 'VARCHAR(64)', 'primary_key': True, 'nullable': False},
        'title': {'type': 'VARCHAR(64)', 'nullable': False},
        'path': {'type': 'VARCHAR(256)', 'nullable': False},
        'publish_time': {'type': 'DATETIME', 'nullable': False},
        'author_id': {'type': 'VARCHAR(64)', 'nullable': False},
        'duration': {'type': 'INT', 'nullable': False},
        'deleted': {'type': 'BOOLEAN', 'nullable': False, 'default': False},
        'category': {'type': 'VARCHAR(12)', 'nullable': True},
        'description': {'type': 'VARCHAR(256)', 'nullable': True},
        'remarks': {'type': 'VARCHAR(256)', 'nullable': True},
        'likes': {'type': 'INT', 'nullable': False, 'default': 0},
        'favorites': {'type': 'INT', 'nullable': False, 'default': 0},
        'reposts': {'type': 'INT', 'nullable': False, 'default': 0}
    }
    
    conn = None
    try:
        conn = get_db_connection()
        
        with conn.cursor() as cursor:
            # 检查表是否存在
            cursor.execute("SHOW TABLES LIKE 'video'")
            result = cursor.fetchone()
            table_exists = result is not None
            
            if not table_exists:
                # 表不存在，创建新表
                create_sql = "CREATE TABLE video ("
                columns_sql = []
                
                # 构建CREATE TABLE语句
                for col_name, col_info in standard_columns.items():
                    col_def = f"{col_name} {col_info['type']}"
                    
                    # 处理NOT NULL约束
                    if not col_info.get('nullable', True):
                        col_def += " NOT NULL"
                    
                    # 处理默认值
                    if 'default' in col_info:
                        default_val = col_info['default']
                        if isinstance(default_val, str):
                            col_def += f" DEFAULT '{default_val}'"
                        else:
                            col_def += f" DEFAULT {default_val}"
                    
                    columns_sql.append(col_def)
                
                # 添加PRIMARY KEY子句
                create_sql += ", ".join(columns_sql) + ", PRIMARY KEY (video_id)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                
                try:
                    cursor.execute(create_sql)
                except Exception as e:
                    # 如果创建失败，尝试先创建表再设置主键
                    create_sql_simple = "CREATE TABLE video (" + ", ".join(columns_sql) + ") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                    cursor.execute(create_sql_simple)
                    cursor.execute("ALTER TABLE video ADD PRIMARY KEY (video_id)")
            else:
                # 表存在，检查并更新列
                # 获取现有表结构
                cursor.execute("SHOW COLUMNS FROM video")
                existing_columns = {row['Field']: row for row in cursor.fetchall()}
                
                # 检查并添加缺失的列
                for col_name, col_info in standard_columns.items():
                    if col_name not in existing_columns:
                        alter_sql = f"ALTER TABLE video ADD COLUMN {col_name} {col_info['type']}"
                        if not col_info.get('nullable', True):
                            alter_sql += " NOT NULL"
                        if 'default' in col_info:
                            default_val = col_info['default']
                            if isinstance(default_val, str):
                                alter_sql += f" DEFAULT '{default_val}'"
                            else:
                                alter_sql += f" DEFAULT {default_val}"
                        cursor.execute(alter_sql)
                
                # 检查表的主键是否正确
                cursor.execute("SHOW KEYS FROM video WHERE Key_name = 'PRIMARY'")
                primary_key = cursor.fetchone()
                if not primary_key or primary_key['Column_name'] != 'video_id':
                    try:
                        # 先删除现有的主键（如果有）
                        if primary_key:
                            cursor.execute("ALTER TABLE video DROP PRIMARY KEY")
                        # 设置正确的主键
                        cursor.execute("ALTER TABLE video ADD PRIMARY KEY (video_id)")
                    except Exception as e:
                        pass
        
        conn.commit()
    except Exception as e:
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

# 数据库操作类
class VideoDatabase:
    """视频数据库操作类 - 提供所有视频相关的数据库操作"""
    
    @staticmethod
    def check_video_exists(video_id, include_deleted=False):
        """检查视频是否存在
        
        Args:
            video_id: 视频ID
            include_deleted: 是否包括已删除的视频
            
        Returns:
            tuple: (是否存在, 是否已删除)
        """
        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cursor:
                if include_deleted:
                    # 检查所有视频（包括已删除的）
                    query = "SELECT COUNT(*) AS count, MAX(CASE WHEN deleted = TRUE THEN 1 ELSE 0 END) AS is_deleted FROM video WHERE video_id = %s"
                else:
                    # 只检查未删除的视频
                    query = "SELECT COUNT(*) AS count, 0 AS is_deleted FROM video WHERE video_id = %s AND deleted = FALSE"
                
                cursor.execute(query, (video_id,))
                result = cursor.fetchone()
                exists = result['count'] > 0
                is_deleted = result['is_deleted'] == 1
                return (exists, is_deleted)
        except Exception as e:
            return (False, False)  # 出错时默认返回不存在
        finally:
            if conn:
                conn.close()
    
    @staticmethod
    def add_video(video_data, path):
        """添加新视频 - 适配新的模型结构，支持恢复已删除的视频"""
        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 1. 先检查是否存在已被删除的相同ID视频
                check_deleted_sql = "SELECT COUNT(*) AS count FROM video WHERE video_id = %s AND deleted = TRUE"
                cursor.execute(check_deleted_sql, (video_data['video_id'],))
                result = cursor.fetchone()
                
                # 获取必需字段（必选）
                title = video_data['title']
                video_path = video_data['path']
                publish_time = video_data['publish_time']
                author_id = video_data['author_id']
                duration = int(video_data['duration'])
                
                # 获取可选字段（如果不存在则使用默认值None）
                description = video_data.get('description', None)
                category = video_data.get('category', None)
                remarks = video_data.get('remarks', None)
                likes = video_data.get('likes', 0)
                favorites = video_data.get('favorites', 0)
                reposts = video_data.get('reposts', 0)
                
                if result['count'] > 0:
                    # 如果存在已删除的视频，则恢复并更新
                    update_sql = '''
                    UPDATE video SET title = %s, description = %s, category = %s, 
                                     author_id = %s, path = %s, publish_time = %s, 
                                     duration = %s, remarks = %s, deleted = FALSE,
                                     likes = %s, favorites = %s, reposts = %s
                    WHERE video_id = %s
                    '''
                    params = (
                        title, description, category,
                        author_id, video_path, publish_time,
                        duration, remarks, 
                        likes, favorites, reposts, video_data['video_id']
                    )
                    cursor.execute(update_sql, params)
                else:
                    # 2. 如果不存在已删除的视频，则尝试插入新视频
                    insert_sql = '''
                    INSERT INTO video (video_id, title, description, category, author_id, 
                                      path, publish_time, duration, remarks, deleted, likes, favorites, reposts)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    '''
                    params = (
                        video_data['video_id'], title, description,
                        category, author_id, video_path,
                        publish_time, duration, remarks,
                        video_data.get('deleted', False), likes,
                        favorites, reposts
                    )
                    cursor.execute(insert_sql, params)
                
                conn.commit()
        except Exception as e:
            if conn:
                conn.rollback()
            raise  # 重新抛出异常以便上层处理
        finally:
            if conn:
                conn.close()
    
    @staticmethod
    def update_video(video_id, video_data, path):
        """更新视频信息 - 适配新的模型结构，支持更新已删除的视频"""
        conn = None
        try:
            # 首先检查视频是否存在（包括已删除的）
            exists, is_deleted = VideoDatabase.check_video_exists(video_id, include_deleted=True)
            
            if not exists:
                # 如果视频不存在，抛出异常或返回错误信息
                raise ValueError(f"视频ID {video_id} 不存在")
            
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 获取必需字段（必选）
                title = video_data['title']
                video_path = video_data['path']
                publish_time = video_data['publish_time']
                author_id = video_data['author_id']
                duration = int(video_data['duration'])
                
                # 获取可选字段（如果不存在则使用默认值None）
                description = video_data.get('description', None)
                category = video_data.get('category', None)
                remarks = video_data.get('remarks', None)
                likes = video_data.get('likes', 0)
                favorites = video_data.get('favorites', 0)
                reposts = video_data.get('reposts', 0)
                
                # 如果视频已删除，在更新时将其恢复
                update_sql = '''
                UPDATE video SET title = %s, description = %s, category = %s, 
                                 author_id = %s, path = %s, publish_time = %s, 
                                 duration = %s, remarks = %s, deleted = FALSE, 
                                 likes = %s, favorites = %s, reposts = %s
                WHERE video_id = %s
                '''
                params = (
                    title, description, category,
                    author_id, video_path, publish_time,
                    duration, remarks, 
                    likes, favorites, reposts, video_id
                )
                affected_rows = cursor.execute(update_sql, params)
                
                conn.commit()
                return affected_rows > 0
        except Exception as e:
            if conn:
                conn.rollback()
            raise
        finally:
            if conn:
                conn.close()
    
    @staticmethod
    def delete_video(video_id):
        """软删除视频，确保能够删除已存在的数据"""
        conn = None
        try:
            # 首先检查视频是否存在（包括已删除的）
            exists, _ = VideoDatabase.check_video_exists(video_id, include_deleted=True)
            
            if not exists:
                # 如果视频不存在，抛出异常
                raise ValueError(f"视频ID {video_id} 不存在")
            
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 执行软删除，无论视频当前状态如何
                delete_sql = "UPDATE video SET deleted = TRUE WHERE video_id = %s"
                affected_rows = cursor.execute(delete_sql, (video_id,))
                
                conn.commit()
                return affected_rows > 0
        except Exception as e:
            if conn:
                conn.rollback()
            raise
        finally:
            if conn:
                conn.close()
    
    @staticmethod
    def get_video(video_id):
        """获取视频信息 - 适配新的模型结构"""
        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 查询视频信息（过滤已删除的视频）
                query_sql = '''
                SELECT video_id, title, description, category, author_id, path, 
                       publish_time, duration, remarks, deleted, likes, favorites, reposts
                FROM video 
                WHERE video_id = %s AND deleted = FALSE
                '''
                cursor.execute(query_sql, (video_id,))
                result = cursor.fetchone()
                
                if not result:
                    return None
                
                # 将数据库结果转换为响应格式，同时保持API兼容性
                response_data = {
                    "video_id": result['video_id'],
                    "title": result['title'],
                    "path": result['path'],  # 使用新的字段名
                    "video_path": result['path'],  # 同时保留旧字段名以保持兼容性
                    "publish_time": result['publish_time'],
                    "author_id": result['author_id'],
                    "duration": result['duration'],
                    "deleted": result.get('deleted', False),
                    "category": result['category'],
                    "description": result['description'],
                    "remarks": result['remarks'],
                    "likes": result.get('likes', 0),
                    "favorites": result.get('favorites', 0),
                    "reposts": result.get('reposts', 0)
                }
                return response_data
        except Exception as e:
            return None
        finally:
            if conn:
                conn.close()